package ar.utn.jess.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;

import ar.utn.jess.common.ConnectionManager;
import ar.utn.jess.common.EditorData;
import ar.utn.jess.exception.SearchException;

import com.google.gson.Gson;

public class EstadisticasServlet extends BaseServlet {

	private static final long serialVersionUID = 1L;
	private static Logger log = Logger.getLogger(EstadisticasServlet.class);

	@Override
	protected void serve(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {

		String action = req.getParameter("action");
		
		if(action==null) {
			
			
			RequestDispatcher rd = getServletContext().getRequestDispatcher("/WEB-INF/pages/estadisticas-filter.jsp");		
			rd.forward(req, resp);
		}
		else if(action.equals("filter")) {
			
			req.setAttribute("sd", req.getParameter("sd"));
			req.setAttribute("ed", req.getParameter("ed"));
			
			RequestDispatcher rd = getServletContext().getRequestDispatcher("/WEB-INF/pages/estadisticas.jsp");		
			rd.forward(req, resp);
		}
		else if(action.equals("barriosOrigen")) {
			getBarriosOrigen(req, resp);
		}
		else if(action.equals("barriosDestino")) {
			getBarriosDestino(req, resp);
		}
		else if(action.equals("viajesPorMes")) {
			getViajesPorMes(req, resp);
		}
		else if(action.equals("viajesPorChofer")) {
			getViajesPorChofer(req, resp);
		}
	}

	
	@SuppressWarnings("unchecked")
	protected EditorData buildEditorData(HttpServletRequest req) {
		EditorData editorData = new EditorData();
		
		Enumeration<String> en = req.getParameterNames();
		while(en.hasMoreElements()) {
			String name = en.nextElement();
			editorData.addAttribute(name, req.getParameter(name));
		}
		
		return editorData;
	}
	
	protected void getBarriosOrigen(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		Enumeration en = req.getParameterNames();
        
        while (en.hasMoreElements()) {
            
            String paramName = (String) en.nextElement();
            System.out.println("PARAMETER: " + paramName + " = " + req.getParameter(paramName) + "<br/>");
            
        }
		
		String sd = req.getParameter("sd");
		String ed = req.getParameter("ed");
		
		Gson gson = new Gson();

		Map<String, Integer> result = new LinkedHashMap<String, Integer>();
		
		String query = "SELECT b.nombre, count(*) FROM viaje v join barrio b on b.id = v.idbarrio_orig where v.fecha_ped >= '"+sd+"' and v.fecha_ped <= '"+ed+"' group by v.idbarrio_orig order by count(*) desc";
		
		Connection conn = null;
		ResultSet rs = executeQuery(query, conn);
		
		try {
			while(rs.next()) {
				
				result.put(rs.getString(1), rs.getInt(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if(conn != null)
				try { conn.close(); } catch (Exception ignore) {}
		}
		
		
		int maxMostrar = 7;
		
		int arraySize = result.size() > maxMostrar ? (maxMostrar+1) : result.size();
		
		Object[][] array = new Object[arraySize][2];
		int i= 0;
		int others = 0;
		
		for(Map.Entry<String, Integer> entry : result.entrySet()) {
			
			if(i < maxMostrar) {
				array[i][0] = entry.getKey();
				array[i][1] = entry.getValue();
			}
			else {
				others += entry.getValue();
			}
			
			i++;
		}
		
		if(result.size() > maxMostrar) {
			array[maxMostrar][0] = "Otros";
			array[maxMostrar][1] = others;
		}
		
		
		resp.setContentType("application/json;charset=utf-8");
		resp.getWriter().write(gson.toJson(array));
	}
	
	protected void getBarriosDestino(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		String sd = req.getParameter("sd");
		String ed = req.getParameter("ed");
		
		Gson gson = new Gson();

		Map<String, Integer> result = new LinkedHashMap<String, Integer>();
		
		String query = "SELECT b.nombre, count(*) FROM viaje v join barrio b on b.id = v.idbarrio_dest where v.fecha_ped >= '"+sd+"' and v.fecha_ped <= '"+ed+"' group by v.idbarrio_dest order by count(*) desc";
		
		Connection conn = null;
		ResultSet rs = executeQuery(query, conn);
		
		try {
			while(rs.next()) {
				
				result.put(rs.getString(1), rs.getInt(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if(conn != null)
				try { conn.close(); } catch (Exception ignore) {}
		}
		
		
		int maxMostrar = 7;
		
		int arraySize = result.size() > maxMostrar ? (maxMostrar+1) : result.size();
		
		Object[][] array = new Object[arraySize][2];
		int i= 0;
		int others = 0;
		
		for(Map.Entry<String, Integer> entry : result.entrySet()) {
			
			if(i < maxMostrar) {
				array[i][0] = entry.getKey();
				array[i][1] = entry.getValue();
			}
			else {
				others += entry.getValue();
			}
			
			i++;
		}
		
		if(result.size() > maxMostrar) {
			array[maxMostrar][0] = "Otros";
			array[maxMostrar][1] = others;
		}
		
		
		resp.setContentType("application/json;charset=utf-8");
		resp.getWriter().write(gson.toJson(array));
	}
	
	protected void getViajesPorMes(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		Gson gson = new Gson();
		Map<Integer, Integer> result = new HashMap<Integer, Integer>();
		
		String query = "SELECT month(fecha_ped), count(*) FROM viaje where estado = 'FINALIZADO' and year(fecha_ped) = 2012 group by month(fecha_ped)";
		
		Connection conn = null;
		ResultSet rs = executeQuery(query, conn);
		
		try {
			while(rs.next()) {
				
				result.put(rs.getInt(1), rs.getInt(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if(conn != null)
				try { conn.close(); } catch (Exception ignore) {}
		}
		
		int[] array = new int[12];
		
		for(int i=0; i<12; i++) {
			
			if(result.get(i+1)!=null) {
				array[i] = result.get(i+1);
			}
			else {
				array[i] = 0;
			}
		}
		
		
//		int[] array = {3,7,9,1,4,6,8,2,5,7,8,4};
		
		
		resp.setContentType("application/json;charset=utf-8");
		resp.getWriter().write(gson.toJson(array));
	}
	
	protected void getViajesPorChofer(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		String sd = req.getParameter("sd");
		String ed = req.getParameter("ed");
		
		Gson gson = new Gson();
		
		Map<String, Object> result = new HashMap<String, Object>();

		
		String query = "SELECT concat(c.nombre,' ',c.apellido), count(*) FROM viaje v join chofer c on c.id = v.idchofer where v.estado = 'FINALIZADO' and v.fecha_ped >= '"+sd+"' and v.fecha_ped <= '"+ed+"' group by idchofer, c.nombre, c.apellido";
		
		Connection conn = null;
		ResultSet rs = executeQuery(query, conn);
		
		try {
			while(rs.next()) {
				
				result.put(rs.getString(1), rs.getInt(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if(conn != null)
				try { conn.close(); } catch (Exception ignore) {}
		}
		
		int[] array = new int[result.size()];
		String[] nombres = new String[result.size()];
		
		int i=0;
		for(Map.Entry<String, Object> entry : result.entrySet()){
			
			nombres[i] = entry.getKey();
			array[i] = new Integer(entry.getValue().toString());
			
			i++;
		}
		
		result.clear();
		
		result.put("s1", array);
		result.put("ticks", nombres);
		
//		int[] array1 = {3,7,9,4};
//		String[] nombres1 = {"Chofer1", "Chofer2", "Chofer3", "Chofer4"};
//		result.clear();
//		result.put("s1", array1);
//		result.put("ticks", nombres1);
		
		resp.setContentType("application/json;charset=utf-8");
		resp.getWriter().write(gson.toJson(result));
	}
	
	private ResultSet executeQuery(String query, Connection conn) {
		
		ResultSet rs = null;
		
		try {			
			conn = ConnectionManager.getConnection();
			Statement stmt = conn.createStatement();						
			
			log.info(query);
			
			rs = stmt.executeQuery(query);
			
		}
		catch(SQLException e) {
			throw new SearchException(e);
		}
		
		
		return rs;
	}
}
